package shiyanshi.qiandao.demo.Mapper;

import org.springframework.stereotype.Service;
import shiyanshi.qiandao.demo.Entity.Qiandao_Records;
import shiyanshi.qiandao.demo.Entity.Record;
import shiyanshi.qiandao.demo.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Description:
 * @Author: Z Y F
 * @CreateDate: 2021/6/19 10:38
 **/
@Service
public class UserMapper {


    public boolean regist( String name,String stuid) throws Exception {
        boolean state = true;
        System.out.println(stuid);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String name1=null;
        String pwd=null;
        //获得数据库连接
        conn = JDBCUtils.getConnection();
        // 创建执行SQL语句的对象
        String sql = "select * from qiandao_user ";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(3));
            name1 = rs.getString(3);
            System.out.println(rs.getString(2));
            pwd = rs.getString(2);

            if(name.equals(name1)&&stuid.equals(pwd)){
                System.out.println("已有账号");
                state = false;
                break;
            }
        }
        if(state){
            String sql2 = "insert into qiandao_user(name,stuid) value(?,?)";
            ps = conn.prepareStatement(sql2);
            ps.setString(1, name);
            ps.setString(2, stuid);
            ps.execute();
        }
        ps.close();
        conn.close();
        return state;
    }

    public boolean denglu(String name, String stuid) {
        boolean state = false;
        System.out.println("dl");
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from qiandao_user";
            ps = conn.prepareStatement(sql);
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(true);
                System.out.println(rs.getString(3));
                System.out.println(rs.getString(2));
                if (name.equals(rs.getString(3)) && stuid.equals(rs.getString(2))) {
                    System.out.println("chenggong");
                    state = true;
                    break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return state;
        }
    }

    public boolean sign(String stuid) {
        boolean state=false;
        Connection conn = null;
        PreparedStatement ps = null;
        List<Qiandao_Records> qiandao_records = new ArrayList<>();
        ResultSet resultSet = null;
        try {
            //获得数据库连接
            conn = JDBCUtils.getConnection();
            // 创建执行SQL语句的对象
//寻找上次签到记录
            String sql_query = "select * from `qiandao_jilu` where qiandao_stuid = ?";
            ps = conn.prepareStatement(sql_query);
            ps.setObject(1, stuid);
            resultSet = ps.executeQuery();
            while (resultSet.next()) { //next判断结果集的下一条是由有数据，如果有返回true且指针后移

                //获取当前这条数据的各个字段值
                int id = resultSet.getInt(1);
                String stuid1 = resultSet.getString(2);
                Date start_time = resultSet.getDate(3);
                Date end_time = resultSet.getDate(4);


                Qiandao_Records qiandao_record = new Qiandao_Records(id, start_time, end_time, stuid1);
                qiandao_records.add(qiandao_record);
            }
            if(qiandao_records.size()==0) {
                String sql = "INSERT INTO qiandao_jilu (qiandao_starttime,qiandao_stuid) VALUES(?,?)";
                ps = conn.prepareStatement(sql);
                //3.填充占位符
                Date nowdate = new Date();
                ps.setObject(1, nowdate);
                ps.setObject(2, stuid);
                //4.执行
                ps.execute();
                state = true;
            }else{
                Qiandao_Records Last_record = qiandao_records.get(qiandao_records.size() - 1);
                if (Last_record.getEnd_time() == null) {
                    System.out.println("上次未结束签到，时间:" + Last_record.getStart_time());
                    return false;
                }else {
                    String sql = "INSERT INTO qiandao_jilu (qiandao_starttime,qiandao_stuid) VALUES(?,?)";
                    ps = conn.prepareStatement(sql);
                    //3.填充占位符
                    Date nowdate = new Date();
                    ps.setObject(1, nowdate);
                    ps.setObject(2, stuid);
                    //4.执行
                    ps.execute();
                    state = true;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            // 务必释放连接资源:
            JDBCUtils.release(ps, conn);
            return state;
        }
    }

    public List<Qiandao_Records> chaxun() throws Exception {
        boolean state=false;
        Connection conn = null;
        PreparedStatement ps = null;
        List<Qiandao_Records> qiandao_records = new ArrayList<>();
        ResultSet resultSet = null;
            //获得数据库连接
            conn = JDBCUtils.getConnection();
            // 创建执行SQL语句的对象
//寻找上次签到记录
            String sql_query = "select * from `qiandao_jilu`";
            ps = conn.prepareStatement(sql_query);
            resultSet = ps.executeQuery();
            while (resultSet.next()) { //next判断结果集的下一条是由有数据，如果有返回true且指针后移

                //获取当前这条数据的各个字段值
                int id = resultSet.getInt(1);
                Date start_time = resultSet.getDate(2);
                Date end_time = resultSet.getDate(3);
                String stuid1 = resultSet.getString(4);

                Qiandao_Records qiandao_record = new Qiandao_Records(id, start_time, end_time, stuid1);
                qiandao_records.add(qiandao_record);
            }
            // 务必释放连接资源:
            JDBCUtils.release(ps, conn);
            return qiandao_records;
    }


    //判断
    public boolean panduan(int qiandao_stuid){
        Connection conn = null;
        Statement stmt = null;
        ResultSet resultSet = null;
//        List<Panduan> userList_Pan = new ArrayList<>();
        try {
            //获得数据库连接
            conn = JDBCUtils.getConnection();
            // 创建执行SQL语句的对象
            String sql = "select * from qiandao_jilu";
            //获取执行sql的对象
            stmt = conn.createStatement();
            //执行sql
            resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {

                int stuid = resultSet.getInt("qiandao_stuid");
                Date starttime = resultSet.getDate("qiandao_starttie");

                String str_stuid = String.valueOf(stuid);
                String str_qiandao_stuid = String.valueOf(qiandao_stuid);

                //判断账号个数是否为10位
                if (str_qiandao_stuid.length()!=10){
                    return false;
                }else {
                    //判断签入时间是否存在
                    if(str_qiandao_stuid==str_stuid&&starttime!=null){
                        return true;
                    }else{
                        return false;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(resultSet,stmt, conn);
        }
        return true;
    }

    public boolean endtime(Integer qiandao_stuid){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        List<Record> records = new ArrayList<>();
        Record record=new Record();
        try {
            //获得数据库连接
            conn = JDBCUtils.getConnection();
            Date date = new Date();
            // 创建执行SQL语句的对象
            String sql_query = "select * from `qiandao_jilu` where qiandao_stuid = ? and qiandao_endtime is null";
            stmt = conn.prepareStatement(sql_query);
            stmt.setObject(1,qiandao_stuid);
            resultSet = stmt.executeQuery();
            if (resultSet==null){
                System.out.println("该学生不存在");
                return false;
            }else{
                while (resultSet.next()) {
                    int id = resultSet.getInt(1);
                    int stuid = resultSet.getInt(2);
                    Date starttime = resultSet.getDate(3);
                    Date endtime = resultSet.getDate(4);
                    record = new Record(id,stuid,starttime,endtime);
                    records.add(record);
                }
                Record Last_record = records.get(records.size() - 1);
                if (Last_record.getQiandao_endtime() != null) {
                    return false;
                } else {
                    String sql = "update qiandao_jilu set qiandao_endtime = ? where qiandao_stuid = ? ";
                    stmt = conn.prepareStatement(sql);
                    stmt.setObject(1,date);
                    stmt.setInt(2,qiandao_stuid);
                    stmt.executeUpdate();
                    return true;
                }
            }
//            String sql = "update qiandao_jilu set qiandao_endtime = ? where qiandao_stuid = ?";
//            stmt = conn.prepareStatement(sql);
//            stmt.setObject(1,date);
//            stmt.setInt(2,qiandao_stuid);
//            //执行sql
//            stmt.executeUpdate();
//            record.setQiandao_stuid(qiandao_stuid);
//            record.setQiandao_endtime(date);
//            System.out.println(record);
            //加入列表
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 务必释放连接资源:
            JDBCUtils.release(stmt,conn);
        }
        return true;
    }
}
